#!/usr/bin/python

""" graduatingSeniors.py
    Pulls info on all the Seniors graduating the next year
    This script should be run in the Fall of every year to give 
    the TCCs an idea of who will be graduating from where. """

__author__ = "Dylan J. Sather"
__created__ = "2009-07-09"
__lastEdited__ = "2009-07-11"

import smtplib
import time
import datetime
import MySQLdb

# Functions
def uniqueValues(list):
    """ Finds unique values in a list
	Arguments: a list of values
	Returns: a list of unique values """

    uniqueList = []
    for item in list:
	found = False
	if item not in uniqueList:
	    found = True
	    uniqueList.append(item)
    return uniqueList

def countOccurrences(list):
    """ Counts the number of occurences of a value in a list
	Arguments: a list
	Returns: a list of the number of occurences of each value in the list """

    occurrences = []
    for item in uniqueValues(list):
	occurrences.append(list.count(item))
    return occurrences

# Get the graduationYear (i.e. next year) and define variables
graduationYear = datetime.date.today().year + 1
certs = []

# Connect to the DB and set cursor; fetch result set
db = MySQLdb.connect(host="localhost", user="user", passwd="passwd", db="db")
cursor = tcdb.cursor()

cursor.execute("SELECT id, username, first_name, last_name, class \
		FROM users \
		WHERE class = '" + str(graduationYear) + "'")

# Start our e-mail message with the total number of graduating Seniors
msg = "A total of " + str(cursor.rowcount) + " seniors are graduating:\n\n"

# And then add the list of graduating Seniors and look for certification info
for user in cursor.fetchall():
    msg += user[2] + " " + user[3] + "\n"

    cursor.execute("SELECT user_id, lab_id \
		    FROM certifications \
		    WHERE user_id = '" + str(user[0]) + "'")

    for cert in cursor.fetchall():
	certs.append(cert[1])

msg += "\n"

labs = uniqueValues(certs)
numberCertified = countOccurrences(certs)

# Then get lab information and add to msg
for i in range(len(labs)):
    cursor.execute("SELECT name \
		    FROM labs \
		    WHERE id = '" + str(labs[i]) + "'")

    labName = cursor.fetchone()

    msg += "We're losing " + str(numberCertified[i]) + " people from " + labName[0] + "\n"

# Send message
fromAddress = "senior.info@grinnell.edu"
toAddress = "tc@grinnell.edu"
subject = "Information on Graduating Seniors"

message = """\
From: %s
To: %s
Subject: %s

%s
""" % (fromAddress, toAddress, subject, msg)
server = smtplib.SMTP('localhost')
server.sendmail(fromAddress, toAddress, message)
server.quit()

# Close up
cursor.close()
tcdb.close()
